<?php

class TblUserController extends Controller{


    /*public static function model($className=__CLASS__)
    {
        return parent::model($className);
    }
    public function tableName()
    {
        return 'tbl_user';
    }*/

    public function actionIndex(){
      /*  $model=new TblUser;
        $model->username='admin';
        $model->password='123456';
        $model->email='leifei@xywy.com';
        var_dump($model->save());*/


        $post=Post::model()->find("id=:id",array(':id'=>1));
        var_dump($post);
        $this->render("index");
    }
    public function actionLogin(){

        $connection=Yii::app()->db;   // 假设你已经建立了一个 "db" 连接

        $sql="select * from tbl_user limit 1";
        $command=$connection->createCommand($sql); //执行sql语句

        $result = $command->queryAll();
        // 如果需要，此 SQL 语句可通过如下方式修改：
        // $command->text=$newSQL;

        var_dump($result);
        exit;
        $this->render("login");    //默认调用公共页头页尾的

        //$this->renderPartial("login");  //不调用嵌套的页头和页尾等公共模板，只调用当前模板

    }
    public function actionUseradd(){
        $taluser_model=new TblUser();
        $this->renderPartial('add',array('taluser_model'=>$taluser_model));
    }
    public function actionAdd(){
        $connection=Yii::app()->db;
        $sql="insert into tbl_user(username,password,email) value('china',md5('china'),'china@qq.com')";
        $command=$connection->createCommand($sql);
        $result=$command->execute();
        $this->renderPartial('add');
    }
    //查询例子
    public function actionSelect(){
        $connection=Yii::app()->db;
        $sql="select * from tbl_user";
        $command=$connection->createCommand($sql);  //执行sql
        $result=$command->query();   //返回结果
        foreach($result as $row){
            echo $row['username']."<br/>";
        }
        exit;
    }


    //事务
    public function actionTin(){
        $connection=Yii::app()->db;
        $sql2="insert into tbl_user(username,password,email) value('usa',md5('china'),'china@qq.com')";
        $sql1="insert into tbl_user(username,password,email) value('en',md5('china'),'china@qq.com')";
        $transaction=$connection->beginTransaction();   //事物开始
        try
        {
            $connection->createCommand($sql1)->execute();
            $connection->createCommand($sql2)->execute();
            //.... other SQL executions
            $transaction->commit();  //提交事物
        }
        catch(Exception $e) // 如果有一条查询失败，则会抛出异常
        {
            $transaction->rollBack();  //事物回滚
        }
        exit;
    }
//绑定参数 防sql注入攻击
    public function actionLei(){
        $connection=Yii::app()->db;
        $username='hubei';
        $email='hubei@qq.com';
        $username2='hubei';
        $email2='hubei@qq.com';
        // 一条带有两个占位符 ":username" 和 ":email"的 SQL
        $sql="INSERT INTO tbl_user (username, email) VALUES(:username,:email)";
        $command=$connection->createCommand($sql);
        // 用实际的用户名替换占位符 ":username"
        $command->bindParam(":username",$username,PDO::PARAM_STR);
        // 用实际的 Email 替换占位符 ":email"
        $command->bindParam(":email",$email,PDO::PARAM_STR);
        $command->execute();
        // 使用新的参数集插入另一行
        $command->bindParam(":username",$username2,PDO::PARAM_STR);
        $command->bindParam(":email",$email2,PDO::PARAM_STR);
        $command->execute();
    }

    public function actionSee(){
        $connection=Yii::app()->db;
        $username='china';
        $email='china@qq.com';
        $sql="SELECT username, email FROM tbl_user";
        $dataReader=$connection->createCommand($sql)->query();
// 使用 $username 变量绑定第一列 (username)
        $dataReader->bindColumn(1,$username);
// 使用 $email 变量绑定第二列 (email)
        $dataReader->bindColumn(2,$email);
var_dump($dataReader->read());
        while($dataReader->read()!==false)
        {
            // $username 和 $email 含有当前行中的 username 和 email
            echo $dataReader['username'];
        }
        exit;
    }

   //Query Builder
    public function actionAn(){
        $id=1;
        $command = Yii::app()->db->createCommand('SELECT * FROM tbl_user');
// the following line will NOT append WHERE clause to the above SQL
        $command->where('id=:id', array(':id'=>$id));

        $result=$command->queryRow();   //返回结果
        var_dump($result);
        exit;

    }
    //连接查询
    public function actionCn(){
        $id=1;
        $user = Yii::app()->db->createCommand()
            ->select('u.id, username, phone')
            ->from('tbl_user u')
            ->join('tbl_profile p', 'u.id=p.id')
            ->where('u.id=:id', array(':id'=>$id))
            ->queryRow();
        var_dump($user);
        exit;

    }
//查询条件写法
   /* public function actionWe(){
        // WHERE id=1 or id=2
        where('id=1 or id=2');
        // WHERE id=:id1 or id=:id2
        where('id=:id1 or id=:id2', array(':id1'=>1, ':id2'=>2));
        // WHERE id=1 OR id=2
        where(array('or', 'id=1', 'id=2'));
        // WHERE id=1 AND (type=2 OR type=3)
        where(array('and', 'id=1', array('or', 'type=2', 'type=3')));
        // WHERE `id` IN (1, 2)
        where(array('in', 'id', array(1, 2));
        // WHERE `id` NOT IN (1, 2)
        where(array('not in', 'id', array(1,2)));
        // WHERE `name` LIKE '%Qiang%'
        where(array('like', 'name', '%Qiang%'));
        // WHERE `name` LIKE '%Qiang' AND `name` LIKE '%Xue'
        where(array('like', 'name', array('%Qiang', '%Xue')));
        // WHERE `name` LIKE '%Qiang' OR `name` LIKE '%Xue'
        where(array('or like', 'name', array('%Qiang', '%Xue')));
        // WHERE `name` NOT LIKE '%Qiang%'
        where(array('not like', 'name', '%Qiang%'));
        // WHERE `name` NOT LIKE '%Qiang%' OR `name` NOT LIKE '%Xue%'
        where(array('or not like', 'name', array('%Qiang%', '%Xue%')));
    }*/
//调用模型方法
    public function actionFn(){
        //模型对象
        $taluser_model=TblUser::model();
        $data=$taluser_model->getOneList();
        var_dump($data);
    }
    public function actionList(){
        $taluser_model=TblUser::model();
        $data=$taluser_model->find();  //每次查询一条信息

        $dataAll=$taluser_model->findAll();//后去全部信息

        $this->renderPartial('list',array('data'=>$data,'dataAll'=>$dataAll));

    }

    public function actionSql(){
        $sql="select * from {{user}}";
        $taluser_model=TblUser::model();
        $data=$taluser_model->findAllBySql($sql);
        var_dump($data);
    }


    public function actionJia(){
        $taluser_model=new TblUser();
        $taluser_model->username='lllll';
        $taluser_model->password=md5('leifei');
        $taluser_model->email='leifei@126.com';
        if($taluser_model->save()){
            echo 'success';
        }else{
            echo 'fail';
        }

    }

}